CS157b Fall 2002Practice Midterm 2

The practice midterm is below. Here are some facts about the actual midterm: (a) The midterm will be in class . (b) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (c) You should bring photo ID. (d) There will be more than one version of the test. Each version will be of comparable difficulty. (e) If your cell-phone or beeper goes off you will be excused from the test at that point and graded on what you have done till your excusal. (f) One problem (less typos) on the actual test will be from the practice test.

1. Give a definition for client-server architecture and describe how
it is typically tasks are typically divided between client and server 
in RDBMSs.

2.  Briefly describe the purpose of each of the three different categories
of views in ORACLE: USER, ALL, and DBA. What kind of information is
stored in USER_TAB_COLUMNS? 

3. What does the following command do in ORACLE?

ANALYZE TABLE ORDERS COMPUTE STATISTICS FOR COLUMNS qty SIZE 5;

4. Explain how sort-merge join works. Give an example.

5. Consider the query below:

SELECT FNAME, LNAME, SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME="MyProduct";

Show what an initial query tree for this query might look like. Then
use the heuristic algorithm from class to optimize this tree then
convert the result to an execution plan.


6. What is the difference between materialized and pipelined query 
evaluation? 

7. Explain with an example how which table is used for the outer loop
in nested loop joins effects the query processing cost.

8. Construct a precedence graph for the following schedule:

r_1(X), r_2(X), w_1(X), r_3(X), w_3(X), r_2(X), w_2(X)

Based on this graph is this schedule serializable? If so, give a 
serialization.

9. What is the purpose of the System Log. When would one use it to undo
a transaction? redo an action?

10. What is the incorrect summary problem. Give an example.